package cn.easier.activity.console.repository;

import cn.easier.activity.web.entity.QuestionnaireAnswersMessage;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Collection;
import java.util.List;

@Repository
public interface QuestionnaireAnswerMessageRepository extends JpaRepository<QuestionnaireAnswersMessage, Integer>, JpaSpecificationExecutor<QuestionnaireAnswersMessage> {

    @Query(value = "select qam.id, " +
            "ai.`name`, " +
            "qm.questionnaire_name, " +
            "qam.answers_message, " +
            "qam.phone_num, " +
            "qam.create_time " +
            "from questionnaire_answers_message qam " +
            "left join questionnaire_message qm on qam.questionnaire_message_id = qm.id " +
            "left join activity_info ai on qm.activity_info_id = ai.id " +
            "where qm.type in ('questionnaire', 'survey') " +
            "      and (:userName is null or ai.create_by = :userName) " +
            "      and (select count(qom.id) from questionnaire_option_message qom where qom.questionnaire_message_id = qm.id) = 0 " +
            "      and (:activityId is null or ai.id = :activityId) " +
            "      and (:title is null or qm.questionnaire_name like :title) " +
            "      and (:phoneNum is null or qam.phone_num = :phoneNum) " +
            "order by ?#{#pageable}",
            countQuery = "select count(qam.id) " +
                    "from questionnaire_answers_message qam " +
                    "left join questionnaire_message qm on qam.questionnaire_message_id = qm.id " +
                    "left join activity_info ai on qm.activity_info_id = ai.id " +
                    "where qm.type in ('questionnaire', 'survey') " +
                    "      and (:userName is null or ai.create_by = :userName) " +
                    "      and (select count(qom.id) from questionnaire_option_message qom where qom.questionnaire_message_id = qm.id) = 0 " +
                    "      and (:activityId is null or ai.id = :activityId) " +
                    "      and (:title is null or qm.questionnaire_name like :title) " +
                    "      and (:phoneNum is null or qam.phone_num = :phoneNum) " +
                    "order by ?#{#pageable}",
            nativeQuery = true)
    Page<Object[]> findAnswerList(@Param("userName") String userName,
                                  @Param("activityId") Integer activityId,
                                  @Param("title") String title,
                                  @Param("phoneNum") String phoneNum,
                                  Pageable pageable);

    @Query(value = "select qam.id, " +
            "ai.`name`, " +
            "qm.questionnaire_name, " +
            "qam.answers_message, " +
            "qam.option_message_id, " +
            "qam.create_time " +
            "from questionnaire_answers_message qam " +
            "left join questionnaire_message qm on qam.questionnaire_message_id = qm.id " +
            "left join activity_info ai on qm.activity_info_id = ai.id " +
            "where qm.type in ('investigation') " +
            "      and (:userName is null or ai.create_by = :userName) " +
            "      and (:activityId is null or ai.id = :activityId) " +
            "      and (:title is null or qm.questionnaire_name like :title) " +
            "      and (:answer is null or qam.answers_message = :answer) " +
            "      or  qam.option_message_id in :idList " +
            "order by ?#{#pageable}",
            countQuery = "select count(qam.id) " +
                    "from questionnaire_answers_message qam " +
                    "left join questionnaire_message qm on qam.questionnaire_message_id = qm.id " +
                    "left join activity_info ai on qm.activity_info_id = ai.id " +
                    "where qm.type in ('investigation') " +
                    "      and (:userName is null or ai.create_by = :userName) " +
                    "      and (:activityId is null or ai.id = :activityId) " +
                    "      and (:title is null or qm.questionnaire_name like :title) " +
                    "      and (:answer is null or qam.answers_message = :answer) " +
                    "      or  qam.option_message_id in :idList " +
                    "order by ?#{#pageable}",
            nativeQuery = true)
    Page<Object[]> findAnswersQuestionnaireList(@Param("userName") String userName,
                                                @Param("activityId") Integer activityId,
                                                @Param("title") String title,
                                                @Param("answer") String answer,
                                                @Param("idList") List<Long> idList,
                                                Pageable pageable);

    @Query(value = "select qam.id, " +
            "ai.`name`, " +
            "qm.questionnaire_name, " +
            "qam.answers_message, " +
            "qam.option_message_id, " +
            "qam.create_time " +
            "from questionnaire_answers_message qam " +
            "left join questionnaire_message qm on qam.questionnaire_message_id = qm.id " +
            "left join activity_info ai on qm.activity_info_id = ai.id " +
            "where qm.type in ('investigation') " +
            "      and (:userName is null or ai.create_by = :userName) " +
            "      and (:activityId is null or ai.id = :activityId) " +
            "      and (:title is null or qm.questionnaire_name like :title) " +
            "      and  qam.option_message_id in :idList " +
            "order by ?#{#pageable}",
            countQuery = "select count(qam.id) " +
                    "from questionnaire_answers_message qam " +
                    "left join questionnaire_message qm on qam.questionnaire_message_id = qm.id " +
                    "left join activity_info ai on qm.activity_info_id = ai.id " +
                    "where qm.type in ('investigation') " +
                    "      and (:userName is null or ai.create_by = :userName) " +
                    "      and (:activityId is null or ai.id = :activityId) " +
                    "      and (:title is null or qm.questionnaire_name like :title) " +
                    "      and  qam.option_message_id in :idList " +
                    "order by ?#{#pageable}",
            nativeQuery = true)
    Page<Object[]> findAnswersQuestionnaireList1(@Param("userName") String userName,
                                                @Param("activityId") Integer activityId,
                                                @Param("title") String title,
                                                @Param("idList") List<Long> idList,
                                                Pageable pageable);

    @Query(value = "select qam.id, " +
            "ai.`name`, " +
            "qm.questionnaire_name, " +
            "qam.answers_message, " +
            "qam.option_message_id, " +
            "qam.create_time " +
            "from questionnaire_answers_message qam " +
            "left join questionnaire_message qm on qam.questionnaire_message_id = qm.id " +
            "left join activity_info ai on qm.activity_info_id = ai.id " +
            "where qm.type in ('investigation') " +
            "      and (:userName is null or ai.create_by = :userName) " +
            "      and (:activityId is null or ai.id = :activityId) " +
            "      and (:title is null or qm.questionnaire_name like :title) " +
            "      and (:answer is null or qam.answers_message = :answer) " +
            "order by ?#{#pageable}",
            countQuery = "select count(qam.id) " +
                    "from questionnaire_answers_message qam " +
                    "left join questionnaire_message qm on qam.questionnaire_message_id = qm.id " +
                    "left join activity_info ai on qm.activity_info_id = ai.id " +
                    "where qm.type in ('investigation') " +
                    "      and (:userName is null or ai.create_by = :userName) " +
                    "      and (:activityId is null or ai.id = :activityId) " +
                    "      and (:title is null or qm.questionnaire_name like :title) " +
                    "      and (:answer is null or qam.answers_message = :answer) " +
                    "order by ?#{#pageable}",
            nativeQuery = true)
    Page<Object[]> findAnswersQuestionnaire(@Param("userName") String userName,
                                                @Param("activityId") Integer activityId,
                                                @Param("title") String title,
                                                @Param("answer") String answer,
                                                Pageable pageable);

    List<QuestionnaireAnswersMessage> findByAnswersMessageAndQuestionnaireMessageIdIn(String answersMessage, Collection<Integer> questionnaireMessageId);

}
